CREATE SCHEMA IF NOT EXISTS CLEAN_ARCHITECTURE;

SET SCHEMA CLEAN_ARCHITECTURE;

/************************************************/
/* EXCHANGE  */
/************************************************/

CREATE TABLE CLEAN_ARCHITECTURE.EXCHANGE (
    ID            NUMBER          NOT NULL,
    CODE          VARCHAR2(10)    NOT NULL,
    NAME          VARCHAR2(100)   NOT NULL,
    POSTCODE      VARCHAR2(10)    NOT NULL,
    CREATED_DATE  DATE
);

ALTER TABLE CLEAN_ARCHITECTURE.EXCHANGE ADD CONSTRAINT EXCHANGE_PK PRIMARY KEY (ID);

CREATE SEQUENCE CLEAN_ARCHITECTURE.EXCHANGE_ID_SEQ MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 CACHE 10 NOCYCLE;

INSERT INTO CLEAN_ARCHITECTURE.EXCHANGE (ID, CODE, NAME, POSTCODE, CREATED_DATE) VALUES (CLEAN_ARCHITECTURE.EXCHANGE_ID_SEQ.nextval, 'EXLON', 'London', 'L1 ABC', SYSDATE);

/************************************************/
/* DEVICE_TYPE  */
/************************************************/

CREATE TABLE CLEAN_ARCHITECTURE.DEVICE_TYPE (
    ID   NUMBER      NOT NULL,
    NAME VARCHAR(50) NOT NULL
);

ALTER TABLE CLEAN_ARCHITECTURE.DEVICE_TYPE ADD CONSTRAINT DEVICE_TYPE_PK PRIMARY KEY (ID);

INSERT INTO CLEAN_ARCHITECTURE.DEVICE_TYPE (ID, NAME) VALUES (1, 'ADSL');
INSERT INTO CLEAN_ARCHITECTURE.DEVICE_TYPE (ID, NAME) VALUES (2, 'FIBRE');

/************************************************/
/* BB_ACCESS_DEVICE  */
/************************************************/

CREATE TABLE CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE (
  ID              NUMBER        NOT NULL,
  EXCHANGE_ID     NUMBER        NOT NULL,
  HOSTNAME        VARCHAR2(100) NOT NULL,
  SERIAL_NUMBER   VARCHAR2(100) NOT NULL,
  DEVICE_TYPE_ID  NUMBER        NOT NULL,
  AVAILABLE_PORTS NUMBER        NOT NULL DEFAULT 100,
  CREATED_DATE    DATE
);

ALTER TABLE CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE ADD CONSTRAINT BB_ACCESS_DEVICE_PK PRIMARY KEY (ID);

ALTER TABLE CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE ADD CONSTRAINT BB_ACCESS_DEVICE_FK1 FOREIGN KEY (EXCHANGE_ID) REFERENCES CLEAN_ARCHITECTURE.EXCHANGE(ID);
ALTER TABLE CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE ADD CONSTRAINT BB_ACCESS_DEVICE_FK2 FOREIGN KEY (DEVICE_TYPE_ID) REFERENCES CLEAN_ARCHITECTURE.DEVICE_TYPE(ID);

CREATE SEQUENCE CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE_ID_SEQ MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 CACHE 10 NOCYCLE;

INSERT INTO CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE (ID, EXCHANGE_ID, HOSTNAME, SERIAL_NUMBER, DEVICE_TYPE_ID, AVAILABLE_PORTS, CREATED_DATE)
    VALUES (CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE_ID_SEQ.nextval, (SELECT ID FROM CLEAN_ARCHITECTURE.EXCHANGE WHERE CODE = 'EXLON'), 'device1.exlon.com', 'AAA111', 1, 100, SYSDATE);
INSERT INTO CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE (ID, EXCHANGE_ID, HOSTNAME, SERIAL_NUMBER, DEVICE_TYPE_ID, AVAILABLE_PORTS, CREATED_DATE)
    VALUES (CLEAN_ARCHITECTURE.BB_ACCESS_DEVICE_ID_SEQ.nextval, (SELECT ID FROM CLEAN_ARCHITECTURE.EXCHANGE WHERE CODE = 'EXLON'), 'device2.exlon.com', 'BBB222', 2, 100, SYSDATE);
